package com.ym.core.utils.excel;

import com.ym.core.exception.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created date on 2018/10/9
 * Author Zy
 */
public class ExcelUtil {
	private final static String Excel_2003 = ".xls"; //2003 版本的excel
	private final static String Excel_2007 = ".xlsx"; //2007 版本的excel


	/**
	 * 描述：根据文件后缀，自动适应上传文件的版本
	 *
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook work = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (Excel_2003.equals(fileType)) {
			work = new HSSFWorkbook(inStr);//2003 版本的excel
		} else if (Excel_2007.equals(fileType)) {
			work = new XSSFWorkbook(inStr);//2007 版本的excel
		} else {
			throw new BusinessException("请选择正确的 excel 文件 `0`！");
		}
		return work;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df1 = new DecimalFormat("0");//格式化number，string字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");//日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
		if (cell != null && !"".equals(cell)) {
			switch (cell.getCellType()) {
				case STRING:
					value = cell.getRichStringCellValue().getString();
					break;
				case NUMERIC:
					if ("General".equals(cell.getCellStyle().getDataFormatString())) {
						value = df1.format(cell.getNumericCellValue());
					} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
						value = sdf.format(cell.getDateCellValue());
					} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						value = sdf.format(date);
					} else {
						value = df2.format(cell.getNumericCellValue());
					}
					break;
				case BOOLEAN:
					value = cell.getBooleanCellValue();
					break;
				case BLANK:
					value = "";
					break;
				default:
					break;
			}
		}
		return value;
	}
}
